﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Recon.Portal.DataObjects;
using System.Data.SqlClient;
using System.Web.Configuration;
namespace Recon.Portal.Data
{
   
    public class BankDAOImpl: BankDAO
    {
         private SqlConnection conn = null;

        public BankDAOImpl()
        {
            object connString = WebConfigurationManager.ConnectionStrings["SqlConnection"];
            if (connString != null)
            {
                conn = new SqlConnection(connString.ToString());
            }
        }
        public List<Bank> getAllBanks()
        {
            String sqlQuery = "select bank_id,bank_name, bank_ifsc, is_enabled from mast_bank_maint where is_enabled = '1'";
            List<Bank> bankLst = null;
            try
            {
                conn.Open();

                SqlCommand cmd = new SqlCommand(sqlQuery, conn);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    int columnIndx = 0;
                    bankLst = new List<Bank>();
                   
                    while (reader.Read())
                    {
                        columnIndx = 0;
                        Bank bank = new Bank
                        {
                        BankID = reader.GetString(columnIndx++),
                        BankName = reader.GetString(columnIndx++),
                        BankIFSC = reader.GetString(columnIndx++),
                        IsEnabled = reader.GetBoolean(columnIndx++)
                        // others need to be populated
                        };
                        bankLst.Add(bank);
                    }
                }


            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return bankLst;
        }
       public Bank getBank(String BankID)
        {
           Bank bank = null;
           String sqlQuery = "select bank_id,bank_name, bank_ifsc, is_enabled from mast_bank_maint where bank_id = '" + BankID + "' and is_enabled = '1'";
            try
            {
                conn.Open();
               
                SqlCommand cmd = new SqlCommand(sqlQuery, conn);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    int columnIndx = 0;
                    bank = new Bank();
                    while (reader.Read())
                    {
                        bank.BankID = reader.GetString(columnIndx++);
                        bank.BankName = reader.GetString(columnIndx++);
                        bank.BankIFSC = reader.GetString(columnIndx++);
                        bank.IsEnabled = reader.GetBoolean(columnIndx++);// (reader.GetByte(columnIndx++) == 1) ? true : false;//Convert.ToBoolean(reader.GetInt32(columnIndx++));
                       // others need to be populated
                    }
                }
              

            }
            catch (Exception exc)
            {
                if (exc != null)
                {
                    throw;
                }
            }
            finally
            {
                conn.Close();
            }

            return bank;
        }
       public int insert(Bank bank)
       {
           int retval = 0;
           int isEnable = (bank.IsEnabled) ? 1 : 0;
           
           string strQuery = "insert into mast_bank_maint(bank_id,bank_name, bank_ifsc, is_enabled," +
               "entered_id,entered_date" +
               ") values ('" + bank.BankID + "','" + bank.BankName + "','" + bank.BankIFSC + "'," + "" + isEnable.ToString() + ",'" + bank.EnteredID + "','" + System.DateTime.Now.ToString("yyyy-MM-dd") + "')";

           try
           {
               conn.Open();
               SqlCommand cmd = new SqlCommand(strQuery, conn);
               retval = cmd.ExecuteNonQuery();
           }
           catch (Exception exc)
           {
               if (exc != null)
               {
                   throw;
               }
           }
           finally
           {
               conn.Close();
           }

           return retval;
       }
       public int updateBank(Bank bank)
       {
           int retval = 0;
           string strQuery = "update mast_bank_maint set bank_name  = '" + bank.BankName + "',bank_ifsc =  '" + bank.BankIFSC + "',is_enabled = " + (bank.IsEnabled ? 1: 0).ToString() + "," +
               "modified_id =  '" + bank.ModifiedID + "',modified_date =  '" + System.DateTime.Now.ToString("yyyy-MM-dd") + "' where  bank_id = '" + bank.BankID + "'";

           try
           {
               conn.Open();
               SqlCommand cmd = new SqlCommand(strQuery, conn);
               retval = cmd.ExecuteNonQuery();
           }
           catch (Exception exc)
           {
               if (exc != null)
               {
                   throw;
               }
           }
           finally
           {
               conn.Close();
           }

           return retval;
       }
       public int deleteBank(Bank bank)
       {
           int retval = 0;
           string strQuery = "";
           strQuery = "Delete From mast_bank_maint  Where bank_id = '" + bank.BankID + "'";
           try
           {
               conn.Open();
               SqlCommand cmd = new SqlCommand(strQuery, conn);
               retval = cmd.ExecuteNonQuery();
           }
           catch (Exception exc)
           {
               if (exc != null)
               {
                   throw;
               }
           }
           finally
           {
               conn.Close();
           }

           return retval;
       }
       public List<Bank> getAllBanksByCustomer(String customerId)
       {
           String sqlQuery = "select bank_id,bank_name,bank_ifsc from mast_bank_maint where " +
                            "bank_id in (select bank_id from mast_customer_account_maint where customer_id = '"+ customerId +"') " +
                            "and is_enabled = '1'";
           List<Bank> bankLst = null;
           try
           {
               conn.Open();

               SqlCommand cmd = new SqlCommand(sqlQuery, conn);
               SqlDataReader reader = cmd.ExecuteReader();
               if (reader.HasRows)
               {
                   int columnIndx = 0;
                   bankLst = new List<Bank>();

                   while (reader.Read())
                   {
                       columnIndx = 0;
                       Bank bank = new Bank();
                       bank.BankID = reader.GetString(columnIndx++);
                       bank.BankName = reader.GetString(columnIndx++);
                       bank.BankIFSC = reader.GetString(columnIndx++);
                       /*bank.BankBIC = reader.GetString(columnIndx++);
                       bank.Branch = reader.GetString(columnIndx++);
                       bank.Address1 = reader.GetString(columnIndx++);
                       bank.Address2 = reader.GetString(columnIndx++);
                       bank.Address3 = reader.GetString(columnIndx++);
                       bank.City = reader.GetString(columnIndx++);
                       bank.State = reader.GetString(columnIndx++);
                       bank.Country = reader.GetString(columnIndx++);
                       bank.ZipCode = reader.GetString(columnIndx++);
                       bank.TelephoneNo = reader.GetString(columnIndx++);
                       bank.EmailID = reader.GetString(columnIndx++);
                       bank.IsEnabled = reader.GetBoolean(columnIndx++);*/
                       // others need to be populated
                       bankLst.Add(bank);
                   }
               }


           }
           catch (Exception exc)
           {
               if (exc != null)
               {
                   throw;
               }
           }
           finally
           {
               conn.Close();
           }

           return bankLst;
       }
    }
}
